Adding Transformation and Lookup Details

You can add transformation and lookup details to a mapping specification in the Mapping Specification grid.

Adding transformation details involves setting up:

  • Business rule
  • Extended business rule transformation

Ensure that you define business rules under the Transformations node for the same ETL Option as the Project ETL. For more information on defining business rules, refer to the Defining Transformations section.

Adding lookup details involves setting up:

  • Trans lookup condition
  • Lookup reference column
  • Lookup on

Ensure that you scan the required table in the Metadata Manager to set trans lookup condition.

Adding Transformation Details

To add business rules to mapping specifications, follow these steps:

  1. Go to Application Menu > Data Catalog > Mapping Manager.
  2. In the Workspace Mappings pane, click a map.

    3. By default, it opens the Mapping Specification tab.

    Transformations Mapping Specification

  3. Right-click the header menu of the Mapping Specification grid.

    4. Right Click Ms Grid

  4. Select the Business Rule check box.

    5. The Business Rule column is now available in the Mapping Specification grid.

  5. Click Icon Edit.

    6. You can now edit the Mapping Specification grid.

  6. Double-click the cell under the Business rule column for the required source column.

    7. The available transformations appear.

    Business Rule Mapping Specification

  7. Select a business rule.

    8. You can add business rules for multiple source columns.

  8. Click Icon Save Exit.

    9. The business rules are added to the mapping specification.

To add extended business rule transformations, follow these steps:

  1. Right-click the header menu of the Mapping Specification grid.

    2. Right Click Ms Grid

  2. Select the Extended Business Rule Transformation check box.

    3. The Extended Business Rule Transformation column is now available in the Mapping Specification grid.

  3. Click Icon Edit.

    4. You can now edit the Mapping Specification grid.

  4. Click Icon Transformation Editor.

    5. The available options appear.

    Extended Transformation Rule Check Box

  5. Select the Extended Business Rule check box.
  6. In the Mapping Specification grid, double-click the cell under the Extended Business rule Transformation column for the required source column.

    7. The Extended Transformation Rule Editor page appears.

    Extended Transformation Rule Editor

  7. Select a pseudocode based on the Project ETL.

    8. For example, if the Project ETL is Informatica then select Informatica Pseudocode.

    Extended Transformation Rule Editor Page

  8. Press Ctrl + Space keys.

    9. The available transformations appear.

    Ctrl Space to Select Br

    If the required transformation is not available in the list, use Icon Create Transformations to create and update the transformations list.

  9. Double-click the required transformation.

    10. You can use Icon Replace Transformation Name to replace the transformation name with the pseudocode.

  10. Click Icon Save User.

    11. The extended business rule transformation is added to the source column. You can add extended business rule transformation to multiple source columns. You can also configure UI labels for user defined fields. For more information on configuring UI labels, refer to the Configuring Language Settings topic.

Adding Lookup Details

To add lookup details in mapping specifications, follow these steps:

  1. Right-click the header menu of the mapping specification grid.
  2. Select Lookup Reference Column, Lookup On, and Trans Lookup Condition.
    3. Trans Lookup Details
  3. Drag the required table from the Metadata Tree View pane and drop it under the Trans Lookup Condition column for the required source column.
    4. Trans Look Up Condition Column

    A SQL query populates.

    Once trans lookup condition is set for the source column, you can add lookup reference column and lookup on.

    To add lookup reference column, double-click the cell under the Lookup Reference Column column and select the required option.

    Look Up Reference Column

    To add lookup on, double-click the cell under the Lookup On column and select the required option.

    Look Up on

  4. Click Icon Save Exit.

    5. The lookup details are added in the Mapping Specification. You can add lookup details for multiple source columns.

Alternately, you can add transformation and lookup details to a mapping specification graphically. For more information about adding transformation and lookup details graphically, refer to the Graphical Designer topic.